﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using DataModels.Common;
using DataModels.Enumerations;
using System.Text.RegularExpressions;
using DataModels.Models;
using GenericTools.OptionBPlus;
using System.Xml;
using System.Xml.Serialization;
using DataModels;
using GenericTools.Models;
using System.Reflection;
using DataModels.Models.Context;

namespace GenericTools
{
    [Flags]
    public enum ExcelTemplateType
    {
        /// <summary>
        /// Template is not used in SAPR or APR
        /// </summary>
        NOT_SAPR_APR = 0,

        /// <summary>
        /// Template is used in SAPR only
        /// </summary>
        SAPR = 1,

        /// <summary>
        /// Template is used in APR only
        /// </summary>
        APR = 2,

        /// <summary>
        /// Template is used in SAPR and APR
        /// </summary>
        SAPR_APR = 3
    }

	public partial class Utilities
    {
        #region Bulk data Retrieval
        public static DataTable GetBulkEachFacilityReport(int reportId, DateTime startDate, DateTime endDate)
		{
			DataTable dataTable = null;
            DbCommand command = null;
            DbParameter param = null;

            DateTime END_OF_NON_OPTION_B_REPORTING_TOOLS_FOR_OPTION_B_PLUS_SITES = DateTime.Parse("2014-03-31");
            DateTime OPTION_B_PLUS_START_DATE = DateTime.Parse("2014-04-01");

			switch (AppConfiguration.AccessMethod)
			{
				case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    
                    param = command.CreateParameter();
                    param.ParameterName = "@StartDate";
                    param.DbType = DbType.DateTime;
                    param.Value = startDate;
                    command.Parameters.Add(param);

                    param = command.CreateParameter();
                    param.ParameterName = "@EndDate";
                    param.DbType = DbType.DateTime;
                    param.Value = endDate;
                    command.Parameters.Add(param);
                    
                    if (endDate < OPTION_B_PLUS_START_DATE)
                    {
                        // Non Option B+ sites
                        dataTable = GetBulkEachFacilityReportCombinedOldTools(reportId, command);
                    }
                    else
                    {
                        // For non-option B+ sites
                        DataTable nonOptionBPlusDataTable = GetBulkEachFacilityReportNonOptionBPlusOnlyOldTools(reportId, command);

                        // For option B+ sites
                        // First fetch those based on old tools
                        command.Parameters.Clear();
                        param = command.CreateParameter();
                        param.ParameterName = "@StartDate";
                        param.DbType = DbType.DateTime;
                        param.Value = startDate;
                        command.Parameters.Add(param);

                        param = command.CreateParameter();
                        param.ParameterName = "@EndDate";
                        param.DbType = DbType.DateTime;
                        param.Value = END_OF_NON_OPTION_B_REPORTING_TOOLS_FOR_OPTION_B_PLUS_SITES;
                        command.Parameters.Add(param);
                        DataTable oldToolsForOptionBPlusDataTable = GetBulkEachFacilityReportOptionBPlusOnlyOldTools(reportId, command);

                        // Now we'll fetch those reports based on new tools
                        command.Parameters.Clear();
                        param = command.CreateParameter();
                        param.ParameterName = "@StartDate";
                        param.DbType = DbType.DateTime;
                        param.Value = OPTION_B_PLUS_START_DATE;
                        command.Parameters.Add(param);

                        param = command.CreateParameter();
                        param.ParameterName = "@EndDate";
                        param.DbType = DbType.DateTime;
                        param.Value = endDate;
                        command.Parameters.Add(param);
                        DataTable newToolsForOptionBPlusDataTable = GetBulkEachFacilityReportOptionBPlusOnlyNewTools(reportId, command);

                        DataTable optionBPlusDataTable = oldToolsForOptionBPlusDataTable.Copy();
                        for (int i = 0; i < optionBPlusDataTable.Rows.Count; i++)
                        {
                            DataRow mainRow = optionBPlusDataTable.Rows[i];
                            DataRow additionalRow = newToolsForOptionBPlusDataTable.Rows[i];
                            AccumulateRowsData(mainRow, additionalRow);
                        }

                        // Now adding to create final output
                        dataTable = nonOptionBPlusDataTable.Copy();
                        for (int i = 0; i < optionBPlusDataTable.Rows.Count; i++)
                        {
                            DataRow row = optionBPlusDataTable.Rows[i];
                            CreateAndAccumulateRowsData(dataTable, row);
                        }
                    }
                    break;
                    
				case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetBulkEachFacilityReport(reportId, startDate, endDate);
                    dataTable = DataTransformation.Deserialize(output);
					break;
			}
			return dataTable;
		}

        /// <summary>
        /// Gets non-option B+ sites based on the old tools
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        private static DataTable GetBulkEachFacilityReportNonOptionBPlusOnlyOldTools(int reportId, DbCommand command)
        {
            #region Report Keywords
            const string KEYWORD_ANC = "ANC";
            const string KEYWORD_PCR = "PCR";
            const string KEYWORD_MAT = "MAT";
            const string KEYWORD_MC = "MC";
            #endregion

            #region Query Definitions
            const string ANC = @"SELECT F.FacCode, S.ANC01, S.ANC02, S.ANC03, S.ANC04, S.ANC05, S.ANC06, S.ANC07, S.ANC08, 
								S.ANC09, S.ANC10, S.ANC11, S.ANC12, S.ANC13, S.ANC14
								FROM Facilities AS F LEFT OUTER JOIN
									(SELECT FacCode, SUM(ISNULL(ANC01, 0)) AS ANC01, SUM(ISNULL(ANC02, 0)) AS ANC02, 
									SUM(ISNULL(ANC03, 0)) AS ANC03, SUM(ISNULL(ANC04, 0)) AS ANC04, SUM(ISNULL(ANC05, 0)) AS ANC05, 
									SUM(ISNULL(ANC06, 0)) AS ANC06, SUM(ISNULL(ANC07, 0)) AS ANC07, SUM(ISNULL(ANC08, 0)) AS ANC08, 
									SUM(ISNULL(ANC09, 0)) AS ANC09, SUM(ISNULL(ANC10, 0)) AS ANC10, SUM(ISNULL(ANC11, 0)) AS ANC11, 
									SUM(ISNULL(ANC12, 0)) AS ANC12, SUM(ISNULL(ANC13, 0)) AS ANC13, SUM(ISNULL(ANC14, 0)) AS ANC14
								FROM ANC
								WHERE (ReportDate BETWEEN @StartDate AND @EndDate)
								GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode NOT IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";

            const string PCR = @"SELECT        F.FacCode, S.PCR01, S.PCR02, S.PCR03, S.PCR04, S.PCR05, S.PCR06, S.PCR07, S.PCR08, S.PCR09, S.PCR10, S.PCR11, S.PCR12, S.PCR13
FROM            Facilities AS F LEFT OUTER JOIN
							 (SELECT        FacCode, SUM(ISNULL(PCR01, 0)) AS PCR01, SUM(ISNULL(PCR02, 0)) AS PCR02, SUM(ISNULL(PCR03, 0)) AS PCR03, SUM(ISNULL(PCR04, 0)) 
														 AS PCR04, SUM(ISNULL(PCR05, 0)) AS PCR05, SUM(ISNULL(PCR06, 0)) AS PCR06, SUM(ISNULL(PCR07, 0)) AS PCR07, SUM(ISNULL(PCR08, 0)) 
														 AS PCR08, SUM(ISNULL(PCR09, 0)) AS PCR09, SUM(ISNULL(PCR10, 0)) AS PCR10, SUM(ISNULL(PCR11, 0)) AS PCR11, SUM(ISNULL(PCR12, 0)) 
														 AS PCR12, SUM(ISNULL(PCR13, 0)) AS PCR13
							   FROM            PCR
							   WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
							   GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode NOT IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";

            const string MAT = @"SELECT        F.FacCode, S.MAT01, S.MAT02, S.MAT03, S.MAT04, S.MAT05, S.MAT06, S.MAT07, S.MAT08, S.MAT09, S.MAT10, S.MAT11, S.MAT12, S.MAT13, S.MAT14, S.MAT15, S.MAT16, S.MAT17, S.MAT18
FROM            Facilities AS F LEFT OUTER JOIN
							 (SELECT        FacCode, SUM(ISNULL(MAT01, 0)) AS MAT01, SUM(ISNULL(MAT02, 0)) AS MAT02, SUM(ISNULL(MAT03, 0)) AS MAT03, SUM(ISNULL(MAT04, 0)) 
														 AS MAT04, SUM(ISNULL(MAT05, 0)) AS MAT05, SUM(ISNULL(MAT06, 0)) AS MAT06, SUM(ISNULL(MAT07, 0)) AS MAT07, SUM(ISNULL(MAT08, 0)) 
														 AS MAT08, SUM(ISNULL(MAT09, 0)) AS MAT09, SUM(ISNULL(MAT10, 0)) AS MAT10, SUM(ISNULL(MAT11, 0)) AS MAT11, SUM(ISNULL(MAT12, 0)) 
														 AS MAT12, SUM(ISNULL(MAT13, 0)) AS MAT13, SUM(ISNULL(MAT14, 0)) AS MAT14, SUM(ISNULL(MAT15, 0)) AS MAT15, SUM(ISNULL(MAT16, 0)) 
														 AS MAT16, SUM(ISNULL(MAT17, 0)) AS MAT17, SUM(ISNULL(MAT18, 0)) AS MAT18
							   FROM            MAT
							   WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
							   GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode NOT IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";

            const string MC = @"SELECT        F.FacCode, S.MC01, S.MC02, S.MC03, S.MC04, S.MC05, S.MC06, S.MC07, S.MC08, S.MC09, S.MC10, S.MC11, S.MC12, S.MC13, S.MC14, S.MC15, S.MC16, S.MC17, S.MC18, S.MC19, S.MC20
FROM            Facilities AS F LEFT OUTER JOIN
							 (SELECT        FacCode, SUM(ISNULL(MC01, 0)) AS MC01, SUM(ISNULL(MC02, 0)) AS MC02, SUM(ISNULL(MC03, 0)) AS MC03, SUM(ISNULL(MC04, 0)) 
														 AS MC04, SUM(ISNULL(MC05, 0)) AS MC05, SUM(ISNULL(MC06, 0)) AS MC06, SUM(ISNULL(MC07, 0)) AS MC07, SUM(ISNULL(MC08, 0)) 
														 AS MC08, SUM(ISNULL(MC09, 0)) AS MC09, SUM(ISNULL(MC10, 0)) AS MC10, SUM(ISNULL(MC11, 0)) AS MC11, SUM(ISNULL(MC12, 0)) 
														 AS MC12, SUM(ISNULL(MC13, 0)) AS MC13, SUM(ISNULL(MC14, 0)) AS MC14, SUM(ISNULL(MC15, 0)) AS MC15, SUM(ISNULL(MC16, 0)) 
														 AS MC16, SUM(ISNULL(MC17, 0)) AS MC17, SUM(ISNULL(MC18, 0)) AS MC18, SUM(ISNULL(MC19, 0)) AS MC19, SUM(ISNULL(MC20, 0)) AS MC20
							   FROM            MC
							   WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
							   GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode NOT IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";
            #endregion

            DataTable dataTable = Utilities.GetReportDetails(reportId);
            DataRow row = dataTable.Rows[0];
            string keyword = row["RepVersion"].ToString();

            if (keyword == KEYWORD_ANC) command.CommandText = ANC;
            if (keyword == KEYWORD_PCR) command.CommandText = PCR;
            if (keyword == KEYWORD_MAT) command.CommandText = MAT;
            if (keyword == KEYWORD_MC) command.CommandText = MC;

            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        /// <summary>
        /// Gets all data but using old tools
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="command"></param>
        /// <returns></returns>
		private static DataTable GetBulkEachFacilityReportCombinedOldTools(int reportId, DbCommand command)
		{
			#region Report Keywords
			const string KEYWORD_ANC = "ANC";
			const string KEYWORD_PCR = "PCR";
			const string KEYWORD_MAT = "MAT";
			const string KEYWORD_MC = "MC";
			#endregion

			#region Query Definitions
			const string ANC = @"SELECT F.FacCode, S.ANC01, S.ANC02, S.ANC03, S.ANC04, S.ANC05, S.ANC06, S.ANC07, S.ANC08, 
								S.ANC09, S.ANC10, S.ANC11, S.ANC12, S.ANC13, S.ANC14
								FROM Facilities AS F LEFT OUTER JOIN
									(SELECT FacCode, SUM(ISNULL(ANC01, 0)) AS ANC01, SUM(ISNULL(ANC02, 0)) AS ANC02, 
									SUM(ISNULL(ANC03, 0)) AS ANC03, SUM(ISNULL(ANC04, 0)) AS ANC04, SUM(ISNULL(ANC05, 0)) AS ANC05, 
									SUM(ISNULL(ANC06, 0)) AS ANC06, SUM(ISNULL(ANC07, 0)) AS ANC07, SUM(ISNULL(ANC08, 0)) AS ANC08, 
									SUM(ISNULL(ANC09, 0)) AS ANC09, SUM(ISNULL(ANC10, 0)) AS ANC10, SUM(ISNULL(ANC11, 0)) AS ANC11, 
									SUM(ISNULL(ANC12, 0)) AS ANC12, SUM(ISNULL(ANC13, 0)) AS ANC13, SUM(ISNULL(ANC14, 0)) AS ANC14
								FROM ANC
								WHERE (ReportDate BETWEEN @StartDate AND @EndDate)
								GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
				ORDER BY F.FacCode";

			const string PCR = @"SELECT        F.FacCode, S.PCR01, S.PCR02, S.PCR03, S.PCR04, S.PCR05, S.PCR06, S.PCR07, S.PCR08, S.PCR09, S.PCR10, S.PCR11, S.PCR12, S.PCR13
FROM            Facilities AS F LEFT OUTER JOIN
							 (SELECT        FacCode, SUM(ISNULL(PCR01, 0)) AS PCR01, SUM(ISNULL(PCR02, 0)) AS PCR02, SUM(ISNULL(PCR03, 0)) AS PCR03, SUM(ISNULL(PCR04, 0)) 
														 AS PCR04, SUM(ISNULL(PCR05, 0)) AS PCR05, SUM(ISNULL(PCR06, 0)) AS PCR06, SUM(ISNULL(PCR07, 0)) AS PCR07, SUM(ISNULL(PCR08, 0)) 
														 AS PCR08, SUM(ISNULL(PCR09, 0)) AS PCR09, SUM(ISNULL(PCR10, 0)) AS PCR10, SUM(ISNULL(PCR11, 0)) AS PCR11, SUM(ISNULL(PCR12, 0)) 
														 AS PCR12, SUM(ISNULL(PCR13, 0)) AS PCR13
							   FROM            PCR
							   WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
							   GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
				ORDER BY F.FacCode";

			const string MAT = @"SELECT        F.FacCode, S.MAT01, S.MAT02, S.MAT03, S.MAT04, S.MAT05, S.MAT06, S.MAT07, S.MAT08, S.MAT09, S.MAT10, S.MAT11, S.MAT12, S.MAT13, S.MAT14, S.MAT15, S.MAT16, S.MAT17, S.MAT18
FROM            Facilities AS F LEFT OUTER JOIN
							 (SELECT        FacCode, SUM(ISNULL(MAT01, 0)) AS MAT01, SUM(ISNULL(MAT02, 0)) AS MAT02, SUM(ISNULL(MAT03, 0)) AS MAT03, SUM(ISNULL(MAT04, 0)) 
														 AS MAT04, SUM(ISNULL(MAT05, 0)) AS MAT05, SUM(ISNULL(MAT06, 0)) AS MAT06, SUM(ISNULL(MAT07, 0)) AS MAT07, SUM(ISNULL(MAT08, 0)) 
														 AS MAT08, SUM(ISNULL(MAT09, 0)) AS MAT09, SUM(ISNULL(MAT10, 0)) AS MAT10, SUM(ISNULL(MAT11, 0)) AS MAT11, SUM(ISNULL(MAT12, 0)) 
														 AS MAT12, SUM(ISNULL(MAT13, 0)) AS MAT13, SUM(ISNULL(MAT14, 0)) AS MAT14, SUM(ISNULL(MAT15, 0)) AS MAT15, SUM(ISNULL(MAT16, 0)) 
														 AS MAT16, SUM(ISNULL(MAT17, 0)) AS MAT17, SUM(ISNULL(MAT18, 0)) AS MAT18
							   FROM            MAT
							   WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
							   GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
				ORDER BY F.FacCode";
			
			const string MC = @"SELECT        F.FacCode, S.MC01, S.MC02, S.MC03, S.MC04, S.MC05, S.MC06, S.MC07, S.MC08, S.MC09, S.MC10, S.MC11, S.MC12, S.MC13, S.MC14, S.MC15, S.MC16, S.MC17, S.MC18, S.MC19, S.MC20
FROM            Facilities AS F LEFT OUTER JOIN
							 (SELECT        FacCode, SUM(ISNULL(MC01, 0)) AS MC01, SUM(ISNULL(MC02, 0)) AS MC02, SUM(ISNULL(MC03, 0)) AS MC03, SUM(ISNULL(MC04, 0)) 
														 AS MC04, SUM(ISNULL(MC05, 0)) AS MC05, SUM(ISNULL(MC06, 0)) AS MC06, SUM(ISNULL(MC07, 0)) AS MC07, SUM(ISNULL(MC08, 0)) 
														 AS MC08, SUM(ISNULL(MC09, 0)) AS MC09, SUM(ISNULL(MC10, 0)) AS MC10, SUM(ISNULL(MC11, 0)) AS MC11, SUM(ISNULL(MC12, 0)) 
														 AS MC12, SUM(ISNULL(MC13, 0)) AS MC13, SUM(ISNULL(MC14, 0)) AS MC14, SUM(ISNULL(MC15, 0)) AS MC15, SUM(ISNULL(MC16, 0)) 
														 AS MC16, SUM(ISNULL(MC17, 0)) AS MC17, SUM(ISNULL(MC18, 0)) AS MC18, SUM(ISNULL(MC19, 0)) AS MC19, SUM(ISNULL(MC20, 0)) AS MC20
							   FROM            MC
							   WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
							   GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
				ORDER BY F.FacCode";
			#endregion

			DataTable dataTable = Utilities.GetReportDetails(reportId);
			DataRow row = dataTable.Rows[0];
			string keyword = row["RepVersion"].ToString();

			if (keyword == KEYWORD_ANC) command.CommandText = ANC;
			if (keyword == KEYWORD_PCR) command.CommandText = PCR;
			if (keyword == KEYWORD_MAT) command.CommandText = MAT;
			if (keyword == KEYWORD_MC) command.CommandText = MC;

            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
		}

        /// <summary>
        /// Gets option B+ sites data based on old tools
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        private static DataTable GetBulkEachFacilityReportOptionBPlusOnlyOldTools(int reportId, DbCommand command)
        {
            #region Report Keywords
            const string KEYWORD_ANC = "ANC";
            const string KEYWORD_PCR = "PCR";
            const string KEYWORD_MAT = "MAT";
            const string KEYWORD_MC = "MC";
            #endregion

            #region Query Definitions
            const string ANC = @"SELECT F.FacCode, S.ANC01, S.ANC02, S.ANC03, S.ANC04, S.ANC05, S.ANC06, S.ANC07, S.ANC08, 
								S.ANC09, S.ANC10, S.ANC11, S.ANC12, S.ANC13, S.ANC14
								FROM Facilities AS F LEFT OUTER JOIN
									(SELECT FacCode, SUM(ISNULL(ANC01, 0)) AS ANC01, SUM(ISNULL(ANC02, 0)) AS ANC02, 
									SUM(ISNULL(ANC03, 0)) AS ANC03, SUM(ISNULL(ANC04, 0)) AS ANC04, SUM(ISNULL(ANC05, 0)) AS ANC05, 
									SUM(ISNULL(ANC06, 0)) AS ANC06, SUM(ISNULL(ANC07, 0)) AS ANC07, SUM(ISNULL(ANC08, 0)) AS ANC08, 
									SUM(ISNULL(ANC09, 0)) AS ANC09, SUM(ISNULL(ANC10, 0)) AS ANC10, SUM(ISNULL(ANC11, 0)) AS ANC11, 
									SUM(ISNULL(ANC12, 0)) AS ANC12, SUM(ISNULL(ANC13, 0)) AS ANC13, SUM(ISNULL(ANC14, 0)) AS ANC14
								FROM ANC
								WHERE (ReportDate BETWEEN @StartDate AND @EndDate)
								GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";

            const string PCR = @"SELECT        F.FacCode, S.PCR01, S.PCR02, S.PCR03, S.PCR04, S.PCR05, S.PCR06, S.PCR07, S.PCR08, S.PCR09, S.PCR10, S.PCR11, S.PCR12, S.PCR13
FROM            Facilities AS F LEFT OUTER JOIN
							 (SELECT        FacCode, SUM(ISNULL(PCR01, 0)) AS PCR01, SUM(ISNULL(PCR02, 0)) AS PCR02, SUM(ISNULL(PCR03, 0)) AS PCR03, SUM(ISNULL(PCR04, 0)) 
														 AS PCR04, SUM(ISNULL(PCR05, 0)) AS PCR05, SUM(ISNULL(PCR06, 0)) AS PCR06, SUM(ISNULL(PCR07, 0)) AS PCR07, SUM(ISNULL(PCR08, 0)) 
														 AS PCR08, SUM(ISNULL(PCR09, 0)) AS PCR09, SUM(ISNULL(PCR10, 0)) AS PCR10, SUM(ISNULL(PCR11, 0)) AS PCR11, SUM(ISNULL(PCR12, 0)) 
														 AS PCR12, SUM(ISNULL(PCR13, 0)) AS PCR13
							   FROM            PCR
							   WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
							   GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";

            const string MAT = @"SELECT        F.FacCode, S.MAT01, S.MAT02, S.MAT03, S.MAT04, S.MAT05, S.MAT06, S.MAT07, S.MAT08, S.MAT09, S.MAT10, S.MAT11, S.MAT12, S.MAT13, S.MAT14, S.MAT15, S.MAT16, S.MAT17, S.MAT18
FROM            Facilities AS F LEFT OUTER JOIN
							 (SELECT        FacCode, SUM(ISNULL(MAT01, 0)) AS MAT01, SUM(ISNULL(MAT02, 0)) AS MAT02, SUM(ISNULL(MAT03, 0)) AS MAT03, SUM(ISNULL(MAT04, 0)) 
														 AS MAT04, SUM(ISNULL(MAT05, 0)) AS MAT05, SUM(ISNULL(MAT06, 0)) AS MAT06, SUM(ISNULL(MAT07, 0)) AS MAT07, SUM(ISNULL(MAT08, 0)) 
														 AS MAT08, SUM(ISNULL(MAT09, 0)) AS MAT09, SUM(ISNULL(MAT10, 0)) AS MAT10, SUM(ISNULL(MAT11, 0)) AS MAT11, SUM(ISNULL(MAT12, 0)) 
														 AS MAT12, SUM(ISNULL(MAT13, 0)) AS MAT13, SUM(ISNULL(MAT14, 0)) AS MAT14, SUM(ISNULL(MAT15, 0)) AS MAT15, SUM(ISNULL(MAT16, 0)) 
														 AS MAT16, SUM(ISNULL(MAT17, 0)) AS MAT17, SUM(ISNULL(MAT18, 0)) AS MAT18
							   FROM            MAT
							   WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
							   GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";

            const string MC = @"SELECT        F.FacCode, S.MC01, S.MC02, S.MC03, S.MC04, S.MC05, S.MC06, S.MC07, S.MC08, S.MC09, S.MC10, S.MC11, S.MC12, S.MC13, S.MC14, S.MC15, S.MC16, S.MC17, S.MC18, S.MC19, S.MC20
FROM            Facilities AS F LEFT OUTER JOIN
							 (SELECT        FacCode, SUM(ISNULL(MC01, 0)) AS MC01, SUM(ISNULL(MC02, 0)) AS MC02, SUM(ISNULL(MC03, 0)) AS MC03, SUM(ISNULL(MC04, 0)) 
														 AS MC04, SUM(ISNULL(MC05, 0)) AS MC05, SUM(ISNULL(MC06, 0)) AS MC06, SUM(ISNULL(MC07, 0)) AS MC07, SUM(ISNULL(MC08, 0)) 
														 AS MC08, SUM(ISNULL(MC09, 0)) AS MC09, SUM(ISNULL(MC10, 0)) AS MC10, SUM(ISNULL(MC11, 0)) AS MC11, SUM(ISNULL(MC12, 0)) 
														 AS MC12, SUM(ISNULL(MC13, 0)) AS MC13, SUM(ISNULL(MC14, 0)) AS MC14, SUM(ISNULL(MC15, 0)) AS MC15, SUM(ISNULL(MC16, 0)) 
														 AS MC16, SUM(ISNULL(MC17, 0)) AS MC17, SUM(ISNULL(MC18, 0)) AS MC18, SUM(ISNULL(MC19, 0)) AS MC19, SUM(ISNULL(MC20, 0)) AS MC20
							   FROM            MC
							   WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
							   GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";
            #endregion

            DataTable dataTable = Utilities.GetReportDetails(reportId);
            DataRow row = dataTable.Rows[0];
            string keyword = row["RepVersion"].ToString();

            if (keyword == KEYWORD_ANC) command.CommandText = ANC;
            if (keyword == KEYWORD_PCR) command.CommandText = PCR;
            if (keyword == KEYWORD_MAT) command.CommandText = MAT;
            if (keyword == KEYWORD_MC) command.CommandText = MC;

            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        /// <summary>
        /// Gets option B+ sites data based on the new tools
        /// </summary>
        /// <param name="reportId"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        private static DataTable GetBulkEachFacilityReportOptionBPlusOnlyNewTools(int reportId, DbCommand command)
        {
            #region Report Keywords
            const string KEYWORD_ANC = "ANC";
            const string KEYWORD_PCR = "PCR";
            const string KEYWORD_MAT = "MAT";
            const string KEYWORD_MC = "MC";
            #endregion

            #region Query Definitions
            const string ANC = @"SELECT F.FacCode, S.ANC01, S.ANC02, S.ANC03, S.ANC04, S.ANC05, S.ANC06, S.ANC07, S.ANC08, 
                S.ANC09, S.ANC10, S.ANC11, S.ANC12, S.ANC13, S.ANC14
                FROM Facilities AS F LEFT OUTER JOIN
	                (SELECT FacCode, SUM(ISNULL([2a]+[2b], 0)) AS ANC01, SUM(ISNULL([5a], 0)) AS ANC02, 
	                SUM(ISNULL([5c], 0)) AS ANC03, SUM(ISNULL([5c], 0)) AS ANC04, SUM(ISNULL([5d], 0)) AS ANC05, 
	                SUM(ISNULL([5i], 0)) AS ANC06, SUM(ISNULL([5j], 0)) AS ANC07, SUM(ISNULL([5c], 0)) AS ANC08, 
	                SUM(ISNULL([5i], 0)) AS ANC09, SUM(ISNULL([5k], 0)) AS ANC10, SUM(ISNULL([5l], 0)) AS ANC11, 
	                SUM(ISNULL([5k], 0)) AS ANC12, SUM(ISNULL([5h], 0)) AS ANC13, SUM(ISNULL([5m], 0)) AS ANC14
                FROM ANC_MTUHA
                WHERE (ReportDate BETWEEN @StartDate AND @EndDate)
                GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";

            const string PCR = @"SELECT        F.FacCode, S.PCR01, S.PCR02, S.PCR03, S.PCR04, S.PCR05, S.PCR06, S.PCR07, S.PCR08, S.PCR09, 
	            S.PCR10, S.PCR11, S.PCR12, S.PCR13
                FROM            Facilities AS F LEFT OUTER JOIN
	            (SELECT        FacCode, SUM(ISNULL([5a]+[5d]+[5j], 0)) AS PCR01, SUM(ISNULL([5p], 0)) AS PCR02, 
		            SUM(ISNULL([5q], 0)) AS PCR03, 0 AS PCR04, 0 AS PCR05, 0 AS PCR06, 0 AS PCR07, 0 AS PCR08, 
		            SUM(ISNULL([5n], 0)) AS PCR09, SUM(ISNULL([5o], 0)) AS PCR10, 0 AS PCR11, 
		            0 AS PCR12, 0 AS PCR13
	            FROM            ANC_MTUHA
	            WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
	            GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";

            const string MAT = @"SELECT        F.FacCode, S.MAT01, S.MAT02, S.MAT03, S.MAT04, S.MAT05, S.MAT06, S.MAT07, S.MAT08, S.MAT09, S.MAT10, 
                CASE WHEN S.MAT11 > S.MAT12 THEN S.MAT11 ELSE S.MAT12 END AS MAT11, 
                S.MAT12, S.MAT13, S.MAT14, S.MAT15, S.MAT16, S.MAT17, S.MAT18
                FROM            Facilities AS F LEFT OUTER JOIN
	                (SELECT        FacCode, SUM(ISNULL([2a]+[2b]+[2c]+[2d], 0)) AS MAT01, SUM(ISNULL([8a], 0)) AS MAT02, 
		                SUM(ISNULL([8b], 0)) AS MAT03, 0 AS MAT04, SUM(ISNULL([8e], 0)) AS MAT05, 0 AS MAT06, 
		                SUM(ISNULL([8f], 0)) AS MAT07, SUM(ISNULL([8b]+[8f], 0)) AS MAT08, SUM(ISNULL([8c], 0)) AS MAT09, 
		                SUM(ISNULL([8g], 0)) AS MAT10, SUM(ISNULL([8g], 0)) AS MAT11, SUM(ISNULL([8h], 0)) AS MAT12, 
		                SUM(ISNULL([8l], 0)) AS MAT13, SUM(ISNULL([8i], 0)) AS MAT14, SUM(ISNULL([8i], 0)) AS MAT15,
		                SUM(ISNULL([8j], 0)) AS MAT16, SUM(ISNULL([8k], 0)) AS MAT17, SUM(ISNULL([8m], 0)) AS MAT18
	                FROM            MAT_MTUHA
	                WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
	                GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";

            const string MC = @"SELECT  F.FacCode, S.MC01, S.MC02, S.MC03, S.MC04, S.MC05, S.MC06, S.MC07, 
                S.MC08, S.MC09, S.MC10, S.MC11, S.MC12, S.MC13, S.MC14, S.MC15, S.MC16, S.MC17, 
                S.MC18, S.MC19, S.MC20
                FROM   Facilities AS F LEFT OUTER JOIN
	                (SELECT        FacCode, SUM(ISNULL(MC01, 0)) AS MC01, SUM(ISNULL(MC02, 0)) AS MC02, SUM(ISNULL(MC03, 0)) AS MC03, 
		                SUM(ISNULL(MC03, 0)) AS MC04, SUM(ISNULL(MC04, 0)) AS MC05, SUM(ISNULL(MC05, 0)) AS MC06, SUM(ISNULL(MC06, 0)) AS MC07, 
		                SUM(ISNULL(MC06, 0)) AS MC08, SUM(ISNULL(MC07, 0)) AS MC09, SUM(ISNULL(MC08+MC10, 0)) AS MC10, SUM(ISNULL(MC11, 0)) AS MC11, 
		                SUM(ISNULL(MC09, 0)) AS MC12, SUM(ISNULL(MC12, 0)) AS MC13, SUM(ISNULL(MC13, 0)) AS MC14, SUM(ISNULL(MC14, 0)) AS MC15, 
		                SUM(ISNULL(MC15, 0)) AS MC16, SUM(ISNULL(MC16, 0)) AS MC17, SUM(ISNULL(MC17, 0)) AS MC18, SUM(ISNULL(MC18, 0)) AS MC19, 
		                SUM(ISNULL(MC19, 0)) AS MC20
	                FROM            MC_MTUHA
	                WHERE        (ReportDate BETWEEN @StartDate AND @EndDate)
	                GROUP BY FacCode) AS S ON F.FacCode = S.FacCode
                WHERE F.FacCode IN (SELECT FacilityCode FROM OptionBPlusFacilities)
				ORDER BY F.FacCode";
            #endregion

            DataTable dataTable = Utilities.GetReportDetails(reportId);
            DataRow row = dataTable.Rows[0];
            string keyword = row["RepVersion"].ToString();

            if (keyword == KEYWORD_ANC) command.CommandText = ANC;
            if (keyword == KEYWORD_PCR) command.CommandText = PCR;
            if (keyword == KEYWORD_MAT) command.CommandText = MAT;
            if (keyword == KEYWORD_MC) command.CommandText = MC;

            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }
        #endregion

        public static DataTable GetTestingAndCounselingReport(params string[] quarters)
        {
            if (quarters == null) return null;
            if (quarters.Length < 1) return null;

            DataTable dataTable = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    dataTable = GetTestingAndCounselingReport(quarters[0]);
                    #region Grown-up Data
                    for (int i = 1; i < quarters.Length; i++)
                    {
                        DataTable temp = GetTestingAndCounselingReport(quarters[i]);
                        // Adding the data into existing datatable
                        foreach (DataRow row in dataTable.Rows)
                        {
                            bool found = false;
                            DataRow missingRow = null;
                            string facilityCode = row[0].ToString();
                            foreach (DataRow r in temp.Rows)
                            {
                                string targetFacilityCode = r[0].ToString();
                                missingRow = r;
                                if (facilityCode == targetFacilityCode)
                                {
                                    found = true;
                                    AccumulateRowsData(row, r);
                                    temp.Rows.Remove(r);
                                    break;
                                }
                            }
                            if (!found) CreateAndAccumulateRowsData(dataTable, missingRow);
                        }
                    }
                    #endregion

                    DataTable infantDataTable = GetTestingAndCounselingReportForInfants(quarters[0]);
                    #region Infant Data
                    for (int i = 1; i < quarters.Length; i++)
                    {
                        DataTable temp = GetTestingAndCounselingReportForInfants(quarters[i]);
                        // Adding the data into existing datatable
                        foreach (DataRow row in infantDataTable.Rows)
                        {
                            bool found = false;
                            DataRow missingRow = null;
                            string facilityCode = row[0].ToString();
                            foreach (DataRow r in temp.Rows)
                            {
                                string targetFacilityCode = r[0].ToString();
                                missingRow = r;
                                if (facilityCode == targetFacilityCode)
                                {
                                    found = true;
                                    AccumulateRowsData(row, r);
                                    temp.Rows.Remove(r);
                                    break;
                                }
                            }
                            if (!found) CreateAndAccumulateRowsData(infantDataTable, missingRow);
                        }
                    }
                    #endregion

                    for (int i = 0; i < dataTable.Rows.Count; i++)
                    {
                        DataRow mainRow = dataTable.Rows[i];
                        DataRow additionalRow = infantDataTable.Rows[i];
                        AccumulateRowsData(mainRow, additionalRow);
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new InvalidOperationException("MS Access database is not supported in this operation");

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetTestingAndCounselingReport(quarters);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }            

            return dataTable;
        }

        private static void CreateAndAccumulateRowsData(DataTable mainTable, DataRow additionalRow)
        {
            object[] rowArray = new object[additionalRow.ItemArray.Length];
            for (int i = 0; i < additionalRow.ItemArray.Length; i++)
            {
                object value = additionalRow.ItemArray[i];
                rowArray[i] = value;
            }

            DataRow mainRow = mainTable.NewRow();
            mainRow.ItemArray = rowArray;
            mainTable.Rows.Add(mainRow);
        }

        private static void AccumulateRowsData(DataRow mainRow, DataRow additionalRow)
        {
            for (int i = 1; i < mainRow.Table.Columns.Count; i++)
            //for (int i = 1; i < mainRow.ItemArray.Length; i++)
            {
                bool converted = default(bool);
                int mainValue;
                int additionalValue;
                //if (mainRow.ItemArray[i] != null)
                if (mainRow[i] != null)
                {                    
                    //converted = int.TryParse(mainRow.ItemArray[i].ToString(), out mainValue);
                    converted = int.TryParse(mainRow[i].ToString(), out mainValue);
                    if (!converted) mainValue = 0;
                } else mainValue = 0;
                
                //if (additionalRow.ItemArray[i] != null)
                if (additionalRow[i] != null)
                {
                    //converted = int.TryParse(additionalRow.ItemArray[i].ToString(), out additionalValue);
                    converted = int.TryParse(additionalRow[i].ToString(), out additionalValue);
                    if (!converted) additionalValue = 0;
                } else additionalValue = 0;

                int newValue = mainValue + additionalValue;
                mainRow.Table.Columns[i].ReadOnly = false;
                mainRow[i] = newValue;
                mainRow.Table.Columns[i].ReadOnly = true;
            }
        }

        private static DataTable GetTestingAndCounselingReport(string quarter)
        {
            const string TC = @"SELECT        Facilities.FacCode, T.TC01_MaleLT15, T.TC01_MaleGT15, T.TC01_FemaleLT15, T.TC01_FemaleGT15, T.TC02_MaleLT15, T.TC02_MaleGT15, T.TC02_FemaleLT15, 
                         T.TC02_FemaleGT15, T.TC03_MaleLT15, T.TC03_MaleGT15, T.TC03_FemaleLT15, T.TC03_FemaleGT15, T.TC04_MaleLT15, T.TC04_MaleGT15, T.TC04_FemaleLT15, 
                         T.TC04_FemaleGT15, T.TC05_MaleLT15, T.TC05_MaleGT15, T.TC05_FemaleLT15, T.TC05_FemaleGT15, T.TC06_MaleLT15, T.TC06_MaleGT15, T.TC06_FemaleLT15, 
                         T.TC06_FemaleGT15, T.TC07_MaleLT15, T.TC07_MaleGT15, T.TC07_FemaleLT15, T.TC07_FemaleGT15, T.TC08_MaleLT15, T.TC08_MaleGT15, T.TC08_FemaleLT15, 
                         T.TC08_FemaleGT15, T.TC09_MaleLT15, T.TC09_MaleGT15, T.TC09_FemaleLT15, T.TC09_FemaleGT15, T.TC10_MaleLT15, T.TC10_MaleGT15, T.TC10_FemaleLT15, 
                         T.TC10_FemaleGT15, T.TC11_MaleLT15, T.TC11_MaleGT15, T.TC11_FemaleLT15, T.TC11_FemaleGT15, T.TC12_MaleLT15, T.TC12_MaleGT15, T.TC12_FemaleLT15, 
                         T.TC12_FemaleGT15
                            FROM  Facilities LEFT OUTER JOIN
                             (SELECT        TestingAndCounselingDatas.FacilityCode, SUM(TestingAndCounselingDatas.TC01_MaleLT15) AS TC01_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC01_FemaleGT15) AS TC01_FemaleGT15, SUM(TestingAndCounselingDatas.TC02_MaleLT15) AS TC02_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC02_MaleGT15) AS TC02_MaleGT15, SUM(TestingAndCounselingDatas.TC02_FemaleLT15) AS TC02_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC02_FemaleGT15) AS TC02_FemaleGT15, SUM(TestingAndCounselingDatas.TC03_MaleLT15) AS TC03_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC03_MaleGT15) AS TC03_MaleGT15, SUM(TestingAndCounselingDatas.TC03_FemaleLT15) AS TC03_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC03_FemaleGT15) AS TC03_FemaleGT15, SUM(TestingAndCounselingDatas.TC04_MaleLT15) AS TC04_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC04_MaleGT15) AS TC04_MaleGT15, SUM(TestingAndCounselingDatas.TC04_FemaleLT15) AS TC04_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC04_FemaleGT15) AS TC04_FemaleGT15, SUM(TestingAndCounselingDatas.TC05_MaleLT15) AS TC05_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC05_MaleGT15) AS TC05_MaleGT15, SUM(TestingAndCounselingDatas.TC05_FemaleLT15) AS TC05_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC05_FemaleGT15) AS TC05_FemaleGT15, SUM(TestingAndCounselingDatas.TC06_MaleLT15) AS TC06_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC06_MaleGT15) AS TC06_MaleGT15, SUM(TestingAndCounselingDatas.TC06_FemaleLT15) AS TC06_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC06_FemaleGT15) AS TC06_FemaleGT15, SUM(TestingAndCounselingDatas.TC07_MaleLT15) AS TC07_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC07_MaleGT15) AS TC07_MaleGT15, SUM(TestingAndCounselingDatas.TC07_FemaleLT15) AS TC07_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC07_FemaleGT15) AS TC07_FemaleGT15, SUM(TestingAndCounselingDatas.TC08_MaleLT15) AS TC08_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC08_MaleGT15) AS TC08_MaleGT15, SUM(TestingAndCounselingDatas.TC08_FemaleLT15) AS TC08_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC08_FemaleGT15) AS TC08_FemaleGT15, SUM(TestingAndCounselingDatas.TC09_MaleLT15) AS TC09_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC09_MaleGT15) AS TC09_MaleGT15, SUM(TestingAndCounselingDatas.TC09_FemaleLT15) AS TC09_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC09_FemaleGT15) AS TC09_FemaleGT15, SUM(TestingAndCounselingDatas.TC10_MaleLT15) AS TC10_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC10_MaleGT15) AS TC10_MaleGT15, SUM(TestingAndCounselingDatas.TC10_FemaleLT15) AS TC10_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC10_FemaleGT15) AS TC10_FemaleGT15, SUM(TestingAndCounselingDatas.TC11_MaleLT15) AS TC11_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC11_MaleGT15) AS TC11_MaleGT15, SUM(TestingAndCounselingDatas.TC11_FemaleLT15) AS TC11_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC11_FemaleGT15) AS TC11_FemaleGT15, SUM(TestingAndCounselingDatas.TC12_MaleLT15) AS TC12_MaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC12_MaleGT15) AS TC12_MaleGT15, SUM(TestingAndCounselingDatas.TC12_FemaleLT15) AS TC12_FemaleLT15, 
                                                         SUM(TestingAndCounselingDatas.TC12_FemaleGT15) AS TC12_FemaleGT15, SUM(TestingAndCounselingDatas.TC01_MaleGT15) AS TC01_MaleGT15, 
                                                         SUM(TestingAndCounselingDatas.TC01_FemaleLT15) AS TC01_FemaleLT15
                               FROM            TestingAndCounselingDatas INNER JOIN
                                                         Timeframes ON TestingAndCounselingDatas.TimeframeId = Timeframes.Id
                               WHERE        (Timeframes.Name = @Timeframe)
                               GROUP BY TestingAndCounselingDatas.FacilityCode) AS T ON Facilities.FacCode = T.FacilityCode";

            DbCommand command = GenericDataAccess.CreateCommand();
            command.CommandText = TC;
            
            DbParameter param = command.CreateParameter();
            param.ParameterName = "@Timeframe";
            param.DbType = DbType.String;
            param.Value = quarter;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);            
            return dataTable;
        }

        private static DataTable GetTestingAndCounselingReportForInfants(string quarter)
        {
            const string TC = @"SELECT        Facilities.FacCode, T.TC01_MaleLT15, T.TC01_MaleGT15, T.TC01_FemaleLT15, T.TC01_FemaleGT15, T.TC02_MaleLT15, T.TC02_MaleGT15, T.TC02_FemaleLT15, 
                         T.TC02_FemaleGT15, T.TC03_MaleLT15, T.TC03_MaleGT15, T.TC03_FemaleLT15, T.TC03_FemaleGT15, T.TC04_MaleLT15, T.TC04_MaleGT15, T.TC04_FemaleLT15, 
                         T.TC04_FemaleGT15, T.TC05_MaleLT15, T.TC05_MaleGT15, T.TC05_FemaleLT15, T.TC05_FemaleGT15, T.TC06_MaleLT15, T.TC06_MaleGT15, T.TC06_FemaleLT15, 
                         T.TC06_FemaleGT15, T.TC07_MaleLT15, T.TC07_MaleGT15, T.TC07_FemaleLT15, T.TC07_FemaleGT15, T.TC08_MaleLT15, T.TC08_MaleGT15, T.TC08_FemaleLT15, 
                         T.TC08_FemaleGT15, T.TC09_MaleLT15, T.TC09_MaleGT15, T.TC09_FemaleLT15, T.TC09_FemaleGT15, T.TC10_MaleLT15, T.TC10_MaleGT15, T.TC10_FemaleLT15, 
                         T.TC10_FemaleGT15, T.TC11_MaleLT15, T.TC11_MaleGT15, T.TC11_FemaleLT15, T.TC11_FemaleGT15, T.TC12_MaleLT15, T.TC12_MaleGT15, T.TC12_FemaleLT15, 
                         T.TC12_FemaleGT15
FROM            Facilities LEFT OUTER JOIN
                             (SELECT        AssociatedEarlyInfantDiagnosis.FacilityCode, 0 AS TC01_MaleLT15, 0 AS TC01_FemaleGT15, SUM(AssociatedEarlyInfantDiagnosis.MC10_Male) 
                                                         AS TC02_MaleLT15, 0 AS TC02_MaleGT15, SUM(AssociatedEarlyInfantDiagnosis.MC10_Female) AS TC02_FemaleLT15, 0 AS TC02_FemaleGT15, 
                                                         SUM(AssociatedEarlyInfantDiagnosis.MC11_Male) AS TC03_MaleLT15, 0 AS TC03_MaleGT15, SUM(AssociatedEarlyInfantDiagnosis.MC11_Female) 
                                                         AS TC03_FemaleLT15, 0 AS TC03_FemaleGT15, 0 AS TC04_MaleLT15, 0 AS TC04_MaleGT15, 0 AS TC04_FemaleLT15, 0 AS TC04_FemaleGT15, 
                                                         SUM(AssociatedEarlyInfantDiagnosis.MC14_Male) AS TC05_MaleLT15, 0 AS TC05_MaleGT15, SUM(AssociatedEarlyInfantDiagnosis.MC14_Female) 
                                                         AS TC05_FemaleLT15, 0 AS TC05_FemaleGT15, SUM(AssociatedEarlyInfantDiagnosis.MC15_Male) AS TC06_MaleLT15, 0 AS TC06_MaleGT15, 
                                                         SUM(AssociatedEarlyInfantDiagnosis.MC15_Female) AS TC06_FemaleLT15, 0 AS TC06_FemaleGT15, 0 AS TC07_MaleLT15, 0 AS TC07_MaleGT15, 
                                                         0 AS TC07_FemaleLT15, 0 AS TC07_FemaleGT15, 0 AS TC08_MaleLT15, 0 AS TC08_MaleGT15, 0 AS TC08_FemaleLT15, 0 AS TC08_FemaleGT15, 
                                                         0 AS TC09_MaleLT15, 0 AS TC09_MaleGT15, 0 AS TC09_FemaleLT15, 0 AS TC09_FemaleGT15, 0 AS TC10_MaleLT15, 0 AS TC10_MaleGT15, 
                                                         0 AS TC10_FemaleLT15, 0 AS TC10_FemaleGT15, 0 AS TC11_MaleLT15, 0 AS TC11_MaleGT15, 0 AS TC11_FemaleLT15, 0 AS TC11_FemaleGT15, 
                                                         0 AS TC12_MaleLT15, 0 AS TC12_MaleGT15, 0 AS TC12_FemaleLT15, 0 AS TC12_FemaleGT15, 0 AS TC01_MaleGT15, 0 AS TC01_FemaleLT15
                               FROM            AssociatedEarlyInfantDiagnosis INNER JOIN
                                                         Timeframes ON AssociatedEarlyInfantDiagnosis.TimeframeId = Timeframes.Id
                               WHERE        (Timeframes.Name = @Timeframe)
                               GROUP BY AssociatedEarlyInfantDiagnosis.FacilityCode) AS T ON Facilities.FacCode = T.FacilityCode";

            DbCommand command = GenericDataAccess.CreateCommand();
            command.CommandText = TC;

            DbParameter param = command.CreateParameter();
            param.ParameterName = "@Timeframe";
            param.DbType = DbType.String;
            param.Value = quarter;
            command.Parameters.Add(param);

            DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        /// <summary>
        /// Creates a list of accessible facilities in the AccessFacilities
        /// </summary>
        /// <param name="selectedFacilities">List of facilities</param>
        public static void CreateAccessibleFacilities(DataTable selectedFacilities)
        {
            // Disable error logging because from this point there may be a lot of error
            // generated by GenericDataAccess method.
            // Later on we'll reactive the logging
            bool logErrrorState = AppConfiguration.EnableErrorLogMail;
            AppConfiguration.EnableErrorLogMail = false;

            DbCommand command = null;
            DbParameter parameter = null;
            string query = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    command.CommandType = CommandType.Text;

                    try
                    {
                        query = "CREATE TABLE AccessFacilities ([FacCode] [nvarchar](50) NOT NULL)";
                        command.CommandText = query;
                        GenericDataAccess.ExecuteNonQuery(command, false);
                    }
                    catch
                    {
                        query = @"TRUNCATE TABLE AccessFacilities";
                        command.CommandText = query;
                        GenericDataAccess.ExecuteNonQuery(command, false);
                    }  

                    command.CommandText = @"INSERT INTO AccessFacilities(FacCode) VALUES(@FacCode)";
                    foreach (DataRow r in selectedFacilities.Rows)
                    {
                        string code = r[0].ToString();

                        command.Parameters.Clear();
                        parameter = command.CreateParameter();
                        parameter.ParameterName = "@FacCode";
                        parameter.DbType = DbType.String;
                        parameter.Value = code;
                        command.Parameters.Add(parameter);
                        GenericDataAccess.ExecuteNonQuery(command, false);
                    }
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                default:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandType = CommandType.Text;
                    try
                    {
                        query = "CREATE TABLE AccessFacilities ([FacCode] [nvarchar](50) NOT NULL)";
                        command.CommandText = query;
                        GenericDataAccess.ExecuteNonQuery(command, false);
                    }
                    catch
                    {
                        query = @"TRUNCATE TABLE AccessFacilities";
                        command.CommandText = query;
                        GenericDataAccess.ExecuteNonQuery(command, false);
                    }                 

                    command.CommandText = @"INSERT INTO AccessFacilities(FacCode) VALUES(@FacCode)";
                    foreach (DataRow r in selectedFacilities.Rows)
                    {
                        string code = r[0].ToString();

                        command.Parameters.Clear();
                        parameter = command.CreateParameter();
                        parameter.ParameterName = "@FacCode";
                        parameter.DbType = DbType.String;
                        parameter.Value = code;
                        command.Parameters.Add(parameter);
                        GenericDataAccess.ExecuteNonQuery(command, false);
                    }
                    if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
                    break;
            }

            // Restoring error logging.
            AppConfiguration.EnableErrorLogMail = logErrrorState;
        }

        #region Other Systems
        public static DataTable GetOtherSystem(int systemId)
        {
            DataTable dataTable = null;
            string query = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = string.Format("SELECT Id, Name, Description, ProvidesFacilityUniqueIdentifier FROM OtherSystems WHERE Id={0}", systemId);
                    dataTable = GenericDataAccess.RunSelectQuery(query);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = string.Format("SELECT Id, Name, Description, ProvidesFacilityUniqueIdentifier FROM OtherSystems WHERE Id={0}", systemId);
                    DbCommand command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandText = query;
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetOtherSystem(systemId);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetOtherSystems()
        {
            DataTable dataTable = null;
            string query = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT [Id], [Name], [Description], [ProvidesFacilityUniqueIdentifier] 
                        FROM [OtherSystems]";
                    dataTable = GenericDataAccess.RunSelectQuery(query);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECT [Id], [Name], [Description], [ProvidesFacilityUniqueIdentifier] 
                        FROM [OtherSystems]";
                    DbCommand command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandText = query;
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetOtherSystems();
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static List<ExcelTemplateReport> GetExcelTemplateReports()
        {
            List<ExcelTemplateReport> reports = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.DIRECT_ACCESS:
                    using (var db = new IQSMS_TestDBContext())
                    {
                        db.Configuration.LazyLoadingEnabled = false;
                        db.Configuration.ProxyCreationEnabled = false;
                        var result = from r in db.ExcelTemplateReports
                                         .Include("ExcelTemplateSheets")
                                         .Include("ExcelTemplateSheets.ExcelTemplateCells")
                                         .Include("ExcelTemplateSheets.ExcelTemplateCells.ExcelTemplateCellExtras")
                                     select r;

                        reports = result.ToList();
                        // To avoid infine recursion during serialization.
                        foreach (var r in reports)
                        {
                            foreach (var s in r.ExcelTemplateSheets)
                            {
                                s.ExcelTemplateReport = null; // Don't reference again to report
                                foreach (var c in s.ExcelTemplateCells)
                                {
                                    c.ExcelTemplateSheet = null;    // Don't reference again to sheet
                                    foreach (var e in c.ExcelTemplateCellExtras) e.ExcelTemplateCell = null;
                                }
                            }
                        }
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new InvalidOperationException("MS Access databases are not supported.");

                case DataAccessMethod.WEB_SERVICES:
                    var output = webService.GetExcelTemplateReports();
                    reports = Services.Deserialize(output, typeof(List<ExcelTemplateReport>)) as List<ExcelTemplateReport>;

                    //StringBuilder sBuilder = new StringBuilder();
                    //using (var xmlWriter = XmlWriter.Create(sBuilder))
                    //{
                    //    var serializer = new XmlSerializer(output.GetType());
                    //    serializer.Serialize(xmlWriter, output);
                    //    var serializedObject = sBuilder.ToString();
                    //    reports = Services.Deserialize(serializedObject, typeof(List<ExcelTemplateReport>)) as List<ExcelTemplateReport>;
                    //}
                    break;
            }
            return reports;
        }

        public static List<ExcelTemplateCellAssociation> GetExcelTemplateSheetCellAssociations(int sheetId)
        {
            List<ExcelTemplateCellAssociation> cellAssociations = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                case DataAccessMethod.DIRECT_ACCESS:
                    using (var db = new IQSMS_TestDBContext())
                    {
                        db.Configuration.LazyLoadingEnabled = false;
                        db.Configuration.ProxyCreationEnabled = false;
                        var result = from r in db.ExcelTemplateCellAssociations
                                         .Include("Cell1")
                                         .Include("Cell2")
                                     where r.Cell1.ExcelTemplateSheet.SheetID == sheetId
                                     select r;

                        cellAssociations = result.ToList();
                        // To avoid infine recursion during serialization.
                        foreach (ExcelTemplateCellAssociation a in cellAssociations)
                        {
                            a.Cell1 = null;
                            a.Cell2.ExcelTemplateCellExtras = null;
                        }
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new InvalidOperationException("MS Access databases are not supported.");

                case DataAccessMethod.WEB_SERVICES:
                    var output = webService.GetExcelTemplateSheetCellAssociation(sheetId);
                    cellAssociations = Services.Deserialize(output, typeof(List<ExcelTemplateCellAssociation>)) as List<ExcelTemplateCellAssociation>;
                    break;
            }
            return cellAssociations;
        }

        #endregion

        #region Setting Holidays For Auto Reminders
        public static DataTable GetCalendarYears()
        {
            DataTable dataTable = null;
            string query = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    query = @"SELECT DISTINCT [CalendarYear] FROM [CalendarHolidays]";
                    dataTable = GenericDataAccess.RunSelectQuery(query);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    query = @"SELECTDISTINCT [CalendarYear] FROM [CalendarHolidays]";
                    DbCommand command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandText = query;
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetCalendarYears();
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static DataTable GetHolidays(int calendarYear)
        {
            string query = String.Format(@"SELECT CalendarYear, HolidayDate, HolidayName 
                FROM CalendarHolidays WHERE CalendarYear={0}", calendarYear);

            DataTable dataTable = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    dataTable = GenericDataAccess.RunSelectQuery(query);
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    DbCommand command = GenericDataAccess.CreateCacheDBCommand();
                    command.CommandText = query;
                    dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetHolidays(calendarYear);
                    dataTable = DataTransformation.Deserialize(output);
                    break;
            }
            return dataTable;
        }

        public static bool SaveHolidays(int calendarYear, List<string> dates, List<string> names)
        {
            if (dates.Count != names.Count) throw new ArgumentException("Holiday dates and names mismatched");
            string deleteQuery = string.Format("DELETE FROM CalendarHolidays WHERE CalendarYear={0}", 
                calendarYear);

            string insertQuery = @"INSERT INTO CalendarHolidays(CalendarYear, HolidayDate, HolidayName)
                VALUES(@CalendarYear, @HolidayDate, @HolidayName)";

            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    int arraySize = names.Count;
                    string[] aDates = new string[arraySize];
                    string[] aNames = new string[arraySize];
                    for (int i = 0; i < arraySize; i++)
                    {
                        aDates[i] = dates[i];
                        aNames[i] = names[i];
                    }

                    string output = webService.SaveHolidays(calendarYear, aDates, aNames);
                    bool saved = bool.Parse(DataTransformation.Decrypt(output));
                    return saved;
            }

            command.CommandType = CommandType.Text;
            command.CommandText = deleteQuery;
            GenericDataAccess.ExecuteNonQuery(command, false);

            command.CommandText = insertQuery;
            int executed = 0;
            for (int i = 0; i < dates.Count; i++)
            {
                string date = dates[i];
                string name = names[i];

                command.Parameters.Clear();
                DbParameter param = command.CreateParameter();
                param.ParameterName = "@CalendarYear";
                param.DbType = DbType.Int32;
                param.Value = calendarYear;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@HolidayDate";
                param.DbType = DbType.String;
                param.Value = date;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@HolidayName";
                param.DbType = DbType.String;
                param.Value = name;
                command.Parameters.Add(param);

                executed += GenericDataAccess.ExecuteNonQuery(command, false);
            }

            if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
            return (executed == names.Count);
        }

        public static bool SaveAutoReminderFacilities(List<string> selectedFacilities)
        {
            string truncateQuery = "TRUNCATE TABLE AutoReminderFacilities";
            string insertQuery = @"INSERT INTO AutoReminderFacilities(FacilityCode) VALUES(@FacilityCode)";

            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    int arraySize = selectedFacilities.Count;
                    string[] aSelectedFacilities = new string[arraySize];
                    for (int i = 0; i < arraySize; i++) aSelectedFacilities[i] = selectedFacilities[i];

                    string output = webService.SaveAutoReminderFacilities(aSelectedFacilities);
                    bool saved = bool.Parse(DataTransformation.Decrypt(output));
                    return saved;
            }

            command.CommandType = CommandType.Text;
            command.CommandText = truncateQuery;
            GenericDataAccess.ExecuteNonQuery(command, false);

            command.CommandText = insertQuery;
            int executed = 0;
            for (int i = 0; i < selectedFacilities.Count; i++)
            {
                string facilityCode = selectedFacilities[i];

                command.Parameters.Clear();
                DbParameter param = command.CreateParameter();
                param.ParameterName = "@FacilityCode";
                param.DbType = DbType.String;
                param.Value = facilityCode;
                command.Parameters.Add(param);

                executed += GenericDataAccess.ExecuteNonQuery(command, false);
            }

            if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
            return (executed == selectedFacilities.Count);
        }

        public static bool SaveAutoReminderDistricts(List<int> selectedDistricts)
        {
            string truncateQuery = "TRUNCATE TABLE AutoReminderDistricts";
            string insertQuery = @"INSERT INTO AutoReminderDistricts(DistrictId) VALUES(@DistrictId)";

            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    int arraySize = selectedDistricts.Count;
                    int[] aSelectedDistricts = new int[arraySize];
                    for (int i = 0; i < arraySize; i++) aSelectedDistricts[i] = selectedDistricts[i];

                    string output = webService.SaveAutoReminderDistricts(aSelectedDistricts);
                    bool saved = bool.Parse(DataTransformation.Decrypt(output));
                    return saved;
            }

            command.CommandType = CommandType.Text;
            command.CommandText = truncateQuery;
            GenericDataAccess.ExecuteNonQuery(command, false);

            command.CommandText = insertQuery;
            int executed = 0;
            for (int i = 0; i < selectedDistricts.Count; i++)
            {
                int districtId = selectedDistricts[i];

                command.Parameters.Clear();
                DbParameter param = command.CreateParameter();
                param.ParameterName = "@DistrictId";
                param.DbType = DbType.Int32;
                param.Value = districtId;
                command.Parameters.Add(param);

                executed += GenericDataAccess.ExecuteNonQuery(command, false);
            }

            if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
            return (executed == selectedDistricts.Count);
        }

        public static bool SaveAutoReminderRegions(List<int> selectedRegions)
        {
            string truncateQuery = "TRUNCATE TABLE AutoReminderRegions";
            string insertQuery = @"INSERT INTO AutoReminderRegions(RegionId) VALUES(@RegionId)";

            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    int arraySize = selectedRegions.Count;
                    int[] aSelectedRegions = new int[arraySize];
                    for (int i = 0; i < arraySize; i++) aSelectedRegions[i] = selectedRegions[i];

                    string output = webService.SaveAutoReminderRegions(aSelectedRegions);
                    bool saved = bool.Parse(DataTransformation.Decrypt(output));
                    return saved;
            }

            command.CommandType = CommandType.Text;
            command.CommandText = truncateQuery;
            GenericDataAccess.ExecuteNonQuery(command, false);

            command.CommandText = insertQuery;
            int executed = 0;
            for (int i = 0; i < selectedRegions.Count; i++)
            {
                int regionId = selectedRegions[i];

                command.Parameters.Clear();
                DbParameter param = command.CreateParameter();
                param.ParameterName = "@RegionId";
                param.DbType = DbType.Int32;
                param.Value = regionId;
                command.Parameters.Add(param);

                executed += GenericDataAccess.ExecuteNonQuery(command, false);
            }

            if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
            return (executed == selectedRegions.Count);
        }

        /// <summary>
        /// Returns the list of facility codes of the facilities which are configured for automatic SMS reminder.
        /// The list is fetched based on the setting in the SysSettings
        /// </summary>
        /// <returns>DataTable object containing the facility codes</returns>
        public static DataTable GetAutoReminderFacilities()
        {
            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetAutoReminderFacilities();
                    DataTable dataTable = DataTransformation.Deserialize(output);
                    return dataTable;
            }

            // Determine the configuration of getting the facilities for auto reminders
            string setting = GetSetting(AutoReminderSettings.AUTO_REMINDER_BASIC_INFO);
            int iSetting = 0;
            bool okToContinue = int.TryParse(setting, out iSetting);
            if (okToContinue)
            {
                string query = null;
                AutoReminderConfiguration config = AutoReminderConfiguration.DEFAULT;
                config = (AutoReminderConfiguration)iSetting;
                if (AutoReminderConfiguration.REMINDED_FACILITIES_BY_FACILITIES ==
                    (config & AutoReminderConfiguration.REMINDED_FACILITIES_BY_FACILITIES))
                {
                    query = "SELECT FacilityCode FROM AutoReminderFacilities";
                }

                if (AutoReminderConfiguration.REMINDED_FACILITIES_BY_DISTRICTS ==
                    (config & AutoReminderConfiguration.REMINDED_FACILITIES_BY_DISTRICTS))
                {
                    query = @"SELECT FacCode AS [FacilityCode] FROM Facilities 
                        WHERE District IN (SELECT DistrictId FROM AutoReminderDistricts)";
                }

                if (AutoReminderConfiguration.REMINDED_FACILITIES_BY_REGIONS ==
                    (config & AutoReminderConfiguration.REMINDED_FACILITIES_BY_REGIONS))
                {
                    query = @"SELECT FacCode AS [FacilityCode] FROM Facilities 
                        WHERE Region IN (SELECT RegionId FROM AutoReminderRegions)";
                }

                command.CommandType = CommandType.Text;
                command.CommandText = query;
                DataTable dataTable = GenericDataAccess.ExecuteSelectCommand(command);
                return dataTable;
            }
            else return new DataTable();
        }

        public static DataTable GetAutoReminderConfiguredFacilities()
        {
            DbCommand command = null;
            DataTable dataTable = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetAutoReminderConfiguredFacilities();
                    dataTable = DataTransformation.Deserialize(output);
                    return dataTable;
            }

            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT FacilityCode FROM AutoReminderFacilities";
            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        public static DataTable GetAutoReminderConfiguredRegions()
        {
            DbCommand command = null;
            DataTable dataTable = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetAutoReminderConfiguredRegions();
                    dataTable = DataTransformation.Deserialize(output);
                    return dataTable;
            }

            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT RegionId FROM AutoReminderRegions";
            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        public static DataTable GetAutoReminderConfiguredDistricts()
        {
            DbCommand command = null;
            DataTable dataTable = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetAutoReminderConfiguredDistricts();
                    dataTable = DataTransformation.Deserialize(output);
                    return dataTable;
            }

            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT DistrictId FROM AutoReminderDistricts";
            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        public static DataTable GetAutoReminderReportMessageTemplates()
        {
            DbCommand command = null;
            DataTable dataTable = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    string output = webService.GetAutoReminderReportMessageTemplates();
                    dataTable = DataTransformation.Deserialize(output);
                    return dataTable;
            }

            string query = @"SELECT ReportId, MessageTemplate, EnableAutoReminder, ReportName, RepVersion 
                FROM AutoReminderReportMessageTemplates INNER JOIN Report 
                ON AutoReminderReportMessageTemplates.ReportId = Report.RepID";
            command.CommandType = CommandType.Text;
            command.CommandText = query;
            dataTable = GenericDataAccess.ExecuteSelectCommand(command);
            return dataTable;
        }

        public static bool SaveAutoReminderReportMessageTemplates(List<bool> enabled, 
            List<int> reportIds, List<string> messageTemplates)
        {
            string truncateQuery = "TRUNCATE TABLE AutoReminderReportMessageTemplates";
            string insertQuery = @"INSERT INTO AutoReminderReportMessageTemplates 
                (ReportId, MessageTemplate, EnableAutoReminder) 
                VALUES(@ReportId, @MessageTemplate, @EnableAutoReminder)";

            DbCommand command = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    command = GenericDataAccess.CreateCommand();
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    command = GenericDataAccess.CreateCacheDBCommand();
                    break;

                case DataAccessMethod.WEB_SERVICES:
                    int arraySize = reportIds.Count;
                    int[] aReportIds = new int[arraySize];
                    bool[] aEnabled = new bool[arraySize];
                    string[] aMessageTemplates = new string[arraySize];

                    for (int i = 0; i < arraySize; i++)
                    {
                        aReportIds[i] = reportIds[i];
                        aEnabled[i] = enabled[i];
                        aMessageTemplates[i] = messageTemplates[i];
                    }

                    string output = webService.SaveAutoReminderReportMessageTemplates(aEnabled, aReportIds, aMessageTemplates);
                    bool saved = bool.Parse(DataTransformation.Decrypt(output));
                    return saved;
            }

            command.CommandType = CommandType.Text;
            command.CommandText = truncateQuery;
            GenericDataAccess.ExecuteNonQuery(command, false);

            command.CommandText = insertQuery;
            int executed = 0;
            for (int i = 0; i < reportIds.Count; i++)
            {
                int reportId = reportIds[i];
                bool enable = enabled[i];
                string messageTemplate = messageTemplates[i];

                command.Parameters.Clear();
                DbParameter param = command.CreateParameter();
                param.ParameterName = "@ReportId";
                param.DbType = DbType.Int32;
                param.Value = reportId;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@MessageTemplate";
                param.DbType = DbType.String;
                param.Value = messageTemplate;
                command.Parameters.Add(param);

                param = command.CreateParameter();
                param.ParameterName = "@EnableAutoReminder";
                param.DbType = DbType.Boolean;
                param.Value = enable;
                command.Parameters.Add(param);

                executed += GenericDataAccess.ExecuteNonQuery(command, false);
            }

            if (command.Connection.State != ConnectionState.Closed) command.Connection.Close();
            return (executed == reportIds.Count);
        }
        #endregion

        #region MTUHA Reports
        public static ReportingError SaveMTUHAReport(string reportingText, string username, string password, string comments,
            bool uploadToDHIS = false, string authorizationText = null)
        {
            //throw new Exception("Data Modification is not allowed at this time. Try again later after APR");
            ReportingError reportingError = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    SMSReportingFormat format = SMSReportingFormat.RESTRICTIVE;
                    ReportingMode mode = ReportingMode.DEFAULT;
                    DataInputingMethod inputMethod = DataInputingMethod.DESKTOP_DATA_CAPTURE;
            
                    bool okToContinue = false;
                    using (var db = new DataModels.Models.Context.IQSMS_TestDBContext())
                    {
                        okToContinue = (from u in db.Users
                                        where u.Username == username &&
                                        u.Password == password &&
                                        u.Deleted == false
                                        select u).Count() == 1;
                    }

                    if (okToContinue)
                    {
                        // Determing the right form validator to use for saving the form.
                        // ANC starts with ANC and
                        // L&D starts with MAT
                        string ancReportKeyword = GetSetting(ANCFormValidator.SETTINGS_KEY_ANC_MTUHA_REPORT_KEYWORDS);
                        if (string.IsNullOrEmpty(ancReportKeyword))
                        {
                            SetSetting(ANCFormValidator.SETTINGS_KEY_ANC_MTUHA_REPORT_KEYWORDS, ANCFormValidator.DEFAULT_ANC_MTUHA_REPORT_KEYWORDS);
                            ancReportKeyword = ANCFormValidator.DEFAULT_ANC_MTUHA_REPORT_KEYWORDS;
                        }

                        string matReportKeyword = GetSetting(LDFormValidator.SETTINGS_KEY_LD_MTUHA_REPORT_KEYWORDS);
                        if (string.IsNullOrEmpty(matReportKeyword))
                        {
                            SetSetting(LDFormValidator.SETTINGS_KEY_LD_MTUHA_REPORT_KEYWORDS, LDFormValidator.DEFAULT_LD_MTUHA_REPORT_KEYWORDS);
                            matReportKeyword = LDFormValidator.DEFAULT_LD_MTUHA_REPORT_KEYWORDS;
                        }

                        string ancKeyword = ancReportKeyword.Split('|')[0];
                        string matKeyword = matReportKeyword.Split('|')[0];
                        bool saved = default(bool);

                        if (reportingText.StartsWith(ancKeyword, StringComparison.OrdinalIgnoreCase))
                        {
                            var formValidator = new ANCFormValidator(reportingText, username, format, mode, inputMethod, password);
                            if (uploadToDHIS) saved = formValidator.SaveAndUploadToDHIS(authorizationText);
                            else saved = formValidator.Save();

                            saved = formValidator.SaveComments(comments);
                            reportingError = formValidator.ReportingError;
                        }

                        if (reportingText.StartsWith(matKeyword, StringComparison.OrdinalIgnoreCase))
                        {
                            var formValidator = new LDFormValidator(reportingText, username, format, mode, inputMethod, password);
                            if (uploadToDHIS) saved = formValidator.SaveAndUploadToDHIS(authorizationText);
                            else saved = formValidator.Save();

                            saved = formValidator.SaveComments(comments);
                            reportingError = formValidator.ReportingError;
                        }

                        if (!saved && reportingError.Type == ReportProcessingError.NO_ERROR)
                        {
                            // Something seriously went wrong because this is not supposed
                            // to happen like this.
                            // TODO: Create more unit test based on this scenario
                            reportingError.Code = ReportingError.INVALID_OBJECT_LABEL;
                            reportingError.KnownError = false;
                            reportingError.ErrorText = "Fatal error occured during saving the report." +
                                " Report this error to development team for resolution.";
                            reportingError.Type = ReportProcessingError.INVALID_OBJECT;

                            var method = MethodInfo.GetCurrentMethod();
                            string innerExceptionMessage = string.Format("{1}: {1}|{2}|{3}",
                                method.Name, reportingText, username, password);
                            var innerException = new FormatException(innerExceptionMessage);

                            var ex = new InvalidOperationException(reportingError.ErrorText, innerException);
                            Utilities.LogError(ex, null);
                        }
                    }
                    else
                    {
                        reportingError = ReportingError.GetReportingError(ReportProcessingError.USER_NOT_AUTHORIZED_FOR_THIS_OPERATION);
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    IQServices.ReportingError status = null;
                    if (uploadToDHIS) status = webService.SaveAndUploadToDHISMTUHAReport(reportingText, username, password, comments, authorizationText);
                    else status = webService.SaveMTUHAReport(reportingText, username, password, comments);

                    if (status == null) return null;
                    reportingError = Services.Deserialize(status, typeof(ReportingError)) as ReportingError;

                    //StringBuilder sBuilder = new StringBuilder();
                    //using (var xmlWriter = XmlWriter.Create(sBuilder))
                    //{
                    //    var ns = new XmlSerializerNamespaces();
                    //    ns.Add("", "");
                    //    var serializer = new XmlSerializer(status.GetType());
                    //    serializer.Serialize(xmlWriter, status, ns);
                    //    var serializedObject = sBuilder.ToString();

                    //    reportingError = Services.Deserialize(serializedObject, typeof(ReportingError)) as ReportingError;
                    //}
                    break;
            }
            return reportingError;
        }

        public static ANCForm GetMTUHAReport(string facilityCode, int reportYear, int reportMonth, bool lessThan20)
        {
            ANCForm report = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new IQSMS_TestDBContext())
                    {
                        report = (from r in db.ANCForms
                                  where r.Year == reportYear &&
                                  r.Month == reportMonth &&
                                  r.FacilityCode == facilityCode &&
                                  r.LessThan20 == lessThan20
                                  select r).FirstOrDefault();
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    var reply = webService.GetMTUHAReport(facilityCode, reportYear, reportMonth, lessThan20);
                    if (reply == null) return null;
                    report = Services.Deserialize(reply, typeof(ANCForm)) as ANCForm;
                    
                    //StringBuilder sBuilder = new StringBuilder();
                    //using (var xmlWriter = XmlWriter.Create(sBuilder))
                    //{
                    //    var serializer = new XmlSerializer(reply.GetType());
                    //    serializer.Serialize(xmlWriter, reply);
                    //    var serializedObject = sBuilder.ToString();

                    //    report = Services.Deserialize(serializedObject, typeof(ANCForm)) as ANCForm;
                    //}
                    break;
            }
            return report;
        }

        public static LDForm GetLDMTUHAReport(string facilityCode, int reportYear, int reportMonth, bool lessThan20)
        {
            LDForm report = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new IQSMS_TestDBContext())
                    {
                        report = (from r in db.LDForms
                                  where r.Year == reportYear &&
                                  r.Month == reportMonth &&
                                  r.FacilityCode == facilityCode &&
                                  r.LessThan20 == lessThan20
                                  select r).FirstOrDefault();
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    var reply = webService.GetLDMTUHAReport(facilityCode, reportYear, reportMonth, lessThan20);
                    if (reply == null) return null;
                    report = Services.Deserialize(reply, typeof(LDForm)) as LDForm;

                    //StringBuilder sBuilder = new StringBuilder();
                    //using (var xmlWriter = XmlWriter.Create(sBuilder))
                    //{
                    //    var serializer = new XmlSerializer(reply.GetType());
                    //    serializer.Serialize(xmlWriter, reply);
                    //    var serializedObject = sBuilder.ToString();

                    //    report = Services.Deserialize(serializedObject, typeof(ANCForm)) as ANCForm;
                    //}
                    break;
            }
            return report;
        }

        public static Facility GetFullFacilityDetails(string facilityCode)
        {
            Facility facility = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new DataModels.Models.Context.IQSMS_TestDBContext())
                    {
                        db.Configuration.LazyLoadingEnabled = false;
                        db.Configuration.ProxyCreationEnabled = false;
                        facility = (from f in db.Facilities
                                          .Include("District")
                                          .Include("District.Region")
                                    where f.FacCode == facilityCode
                                    select f).FirstOrDefault();

                        // Removing circular reference
                        if (facility.District != null) facility.District.Facilities = null;
                        //if (facility.Region != null) facility.Region.Facilities = null;
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    var status = webService.GetFullFacilityDetails(facilityCode);
                    if (status == null) return null;
                    facility = Services.Deserialize(status, typeof(Facility)) as Facility;

                    //StringBuilder sBuilder = new StringBuilder();
                    //using (var xmlWriter = XmlWriter.Create(sBuilder))
                    //{
                    //    var serializer = new XmlSerializer(status.GetType());
                    //    serializer.Serialize(xmlWriter, status);
                    //    var serializedObject = sBuilder.ToString();

                    //    facility = Services.Deserialize(serializedObject, typeof(Facility)) as Facility;
                    //}
                    break;
            }
            return facility;
        }

        public static List<Region> GetDetailedAllRegions(int supportingPartnerId, string userSignature)
        {
            List<Region> allRegions = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new DataModels.Models.Context.IQSMS_TestDBContext())
                    {
                        db.Configuration.LazyLoadingEnabled = false;
                        db.Configuration.ProxyCreationEnabled = false;
                        var results = from r in db.Regions
                                          .Include("Districts")
                                          .Include("Districts.Facilities")
                                      select r;

                        var regions = results.ToList();
                        // Removing circular reference
                        //foreach (var region in regions)
                        //{
                        //    foreach (var district in region.Districts) district.Region = null;
                        //    foreach (var facility in region.Facilities)
                        //    {
                        //        facility.District = null;
                        //        facility.Region = null;
                        //    }
                        //    region.Facilities = null;
                        //}
                        //return regions.ToList();
                        allRegions = regions;
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    var status = webService.GetDetailedAllRegions(supportingPartnerId, userSignature);
                    allRegions = Services.Deserialize(status, typeof(List<Region>)) as List<Region>;

                    //StringBuilder sBuilder = new StringBuilder();
                    //using (var xmlWriter = XmlWriter.Create(sBuilder))
                    //{
                    //    var serializer = new XmlSerializer(status.GetType());
                    //    serializer.Serialize(xmlWriter, status);
                    //    var serializedObject = sBuilder.ToString();

                    //    allRegions = Services.Deserialize(serializedObject, typeof(List<Region>)) as List<Region>;
                    //}
                    break;
            }

            return allRegions;
        }
        #endregion

        public static OptionBPlusFacility GetOptionBPlusFacilityInfo(string facilityCode)
        {
            OptionBPlusFacility info = null;
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new IQSMS_TestDBContext())
                    {
                        info = (from r in db.OptionBPlusFacilities
                                where r.FacilityCode == facilityCode
                                select r).FirstOrDefault();
                    }
                    break;

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    var status = webService.GetOptionBPlusFacilityInfo(facilityCode);
                    if (status != null)
                    {
                        info = Services.Deserialize(status, typeof(OptionBPlusFacility)) as OptionBPlusFacility;
                    }
                    break;
            }
            return info;
        }

        public static bool SaveOptionBPlusServices(OptionBPlusFacility facilityInfo)
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new DataModels.Models.Context.IQSMS_TestDBContext())
                    {
                        var info = (from s in db.OptionBPlusFacilities
                                    where s.FacilityCode == facilityInfo.FacilityCode
                                    select s).FirstOrDefault();
                        if (info == null)
                        {
                            var i = new OptionBPlusFacility
                            {
                                CTCStartDate = facilityInfo.CTCStartDate,
                                DateStarted = facilityInfo.DateStarted,
                                DateTrained = facilityInfo.DateTrained,
                                FacilityCode = facilityInfo.FacilityCode,
                                HasCTC = facilityInfo.HasCTC
                            };
                            db.OptionBPlusFacilities.Add(i);
                            db.SaveChanges();
                        }
                        else
                        {
                            info.CTCStartDate = facilityInfo.CTCStartDate;
                            info.DateStarted = facilityInfo.DateStarted;
                            info.DateTrained = facilityInfo.DateTrained;
                            info.HasCTC = facilityInfo.HasCTC;
                            db.SaveChanges();
                        }
                        return true;
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    var f = new IQServices.OptionBPlusFacility
                    {
                        CTCStartDate = facilityInfo.CTCStartDate,
                        DateStarted = facilityInfo.DateStarted,
                        DateTrained = facilityInfo.DateTrained,
                        FacilityCode = facilityInfo.FacilityCode,
                        HasCTC = facilityInfo.HasCTC
                    };

                    var status = webService.SaveOptionBPlusServices(f);
                    return status;
            }
            return false;
        }

        public static bool SaveReportMergingDefinition(string name, string description, 
            int destinationReportId, List<int> sourceReportIds, List<string> indicatorDefinitions)
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new IQSMS_TestDBContext())
                    {
                        // get the list of report indicators of the destination report.
                        var reportInfo = (from r in db.ReportDataLocations
                                          where r.RepID == destinationReportId
                                          select r).ToList();
                        if (reportInfo.Count < 4) throw new Exception("Report is either invalid or missing its indicators");
                        else
                        {
                            if (reportInfo.Count != indicatorDefinitions.Count)
                            {
                                throw new Exception("Report indicators count does not match with the supplied indicator definition");
                            }
                        }

                        // Is this new or old merging report?
                        var mergingInfo = (from m in db.ReportMerges
                                           where m.Name == name
                                           select m).FirstOrDefault();
                        if (mergingInfo != null)
                        {
                            db.ReportMerges.Remove(mergingInfo);
                            db.SaveChanges();
                        }

                        var mergingReports = new List<MergingReport>();
                        foreach (int sourceReportId in sourceReportIds)
                        {
                            var mergingReport = new MergingReport
                            {
                                DestinationReportId = destinationReportId,
                                SourceReportId = sourceReportId,
                            };
                            mergingReports.Add(mergingReport);
                        }

                        var reportDataMergings = new List<ReportDataMerging>();
                        for (int i = 0; i < indicatorDefinitions.Count; i++)
                        {
                            string indicatorDefinition = indicatorDefinitions[i];
                            string destinationIndicator = string.Format("{{{0}}}.{1}", reportInfo[i].RepID, reportInfo[i].ShortCode);

                            var reportDataMerging = new ReportDataMerging
                            {
                                DestinationIndicator = destinationIndicator,
                                SourceIndicator = indicatorDefinition
                            };
                            reportDataMergings.Add(reportDataMerging);
                        }

                        mergingInfo = new ReportMerge
                        {
                            Name = name,
                            Description = description,
                            MergingReports = mergingReports,
                            ReportDataMerging = reportDataMergings
                        };
                        db.ReportMerges.Add(mergingInfo);
                        db.SaveChanges();
                        return true;
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    return webService.SaveReportMergingDefinition(name, description, destinationReportId, 
                        sourceReportIds.ToArray(), indicatorDefinitions.ToArray());
            }

            return false;
        }

        public static ReportMerge GetReportMergeDetails(int reportMergeId)
        {
            switch (AppConfiguration.AccessMethod)
            {
                case DataAccessMethod.DIRECT_ACCESS:
                case DataAccessMethod.OFFLINE_DIRECT_ACCESS:
                    using (var db = new IQSMS_TestDBContext())
                    {
                        db.Configuration.LazyLoadingEnabled = false;
                        db.Configuration.ProxyCreationEnabled = false;

                        var result = (from r in db.ReportMerges
                                          .Include("MergingReports")
                                          .Include("ReportDataMerging")
                                      where r.MergeId == reportMergeId
                                      select r).FirstOrDefault();
                        if (result != null)
                        {
                            foreach (var i in result.MergingReports) i.ReportMerge = null;
                            foreach (var i in result.ReportDataMerging) i.ReportMerge = null;
                        }
                        return result;
                    }

                case DataAccessMethod.OFFLINE_DATA_ACCESS:
                    throw new NotImplementedException("This feature does not support MS Access back-end");

                case DataAccessMethod.WEB_SERVICES:
                    var status = webService.GetReportMergeDetails(reportMergeId);
                    var output = Services.Deserialize(status, typeof(ReportMerge)) as ReportMerge;
                    return output;
            }

            return null;
        }
    }
}
